﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Entidades;

namespace DAO
{
    public static class ReservarDAO
    {
        public static int insertReserva(string user, int cant, double costo)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            int id = 0;
            try
            {
                cn.Open();
                cmd.CommandText = "insert into Reserva(userName,fecha,cantidad,costo) values (@user,@fecha,@cant,@costo); select @@identity;";
                cmd.Parameters.AddWithValue("@user", user);
                cmd.Parameters.AddWithValue("@fecha", DateTime.Now.Date);
                cmd.Parameters.AddWithValue("@cant", cant);
                cmd.Parameters.AddWithValue("@costo", costo);
                id = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch (Exception ex)
            {
                id = 0;
                throw new ApplicationException("Error al insertar  la reserva:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return id;

        }
        public static void insertEntrada(Funcion f, int idR)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            try
            {
                cn.Open();
                cmd.CommandText = "insert into Entrada(id_Pelicula,id_Cine,numero_Sala,id_Horario,fecha,id_Reserva,precio) values (@IdP,@IdC,@IdS,@IdH,@Fec,@IdR,@Pre)";
                cmd.Parameters.AddWithValue("@IdP", f.IdPelicula);
                cmd.Parameters.AddWithValue("@IdC", f.IdCine);
                cmd.Parameters.AddWithValue("@IdS", f.NumeroSala);
                cmd.Parameters.AddWithValue("@IdH", f.IdHorario);
                cmd.Parameters.AddWithValue("@Fec", f.Fecha);
                cmd.Parameters.AddWithValue("@IdR", idR);
                cmd.Parameters.AddWithValue("@Pre", f.Precio);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al insertar el la entrada:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }

        }
        public static Reserva buscarPorId(int id)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            Reserva r = new Reserva();
            try
            {
                cn.Open();
                cmd.CommandText = "select id_Reserva,userName,fecha,cantidad,costo from Reserva where id_Reserva=@Id";
                cmd.Parameters.AddWithValue("@Id", id);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    r.IdReserva=int.Parse(dr["id_Reserva"].ToString());
                    r.UserName = dr["userName"].ToString();
                    r.Fecha = DateTime.Parse(dr["fecha"].ToString());
                    r.Cantidad = int.Parse(dr["cantidad"].ToString());
                    r.Costo = double.Parse(dr["costo"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al buscar la reserva:" + ex.Message);

            }
            finally
            {
                cn.Close();
            }
            return r;

        }
        public static DataTable cargarGrilla(string id,string orden)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = cn;
            DataTable dt = new DataTable();
            try
            {
                cn.Open();
                cmd.CommandText = "select Distinct r.id_Reserva,r.fecha,p.nombre,e.fecha,h.horario_Inicio,c.nombre,e.numero_Sala,r.cantidad,r.costo from Cine c,Pelicula p, Entrada e, Horario h,Reserva r where r.id_Reserva=e.id_Reserva and e.id_Pelicula=p.id_Pelicula and e.id_Horario=h.id_Horario and e.id_Cine=c.id_Cine  and r.userName like @Id and r.fecha < @fecha";
                cmd.Parameters.AddWithValue("@Id", id);
                cmd.Parameters.AddWithValue("@fecha", DateTime.Now.Date);
                if (orden == "")
                    cmd.CommandText += " order by r.fecha";
                else
                {
                    if (orden.Equals("nombre1"))
                    {
                        cmd.CommandText += " order by c.nombre";
                    }
                    else
                    {
                        if (orden.Equals("nombre"))
                        {
                            cmd.CommandText += " order by p.nombre";
                        }
                        else
                        {
                            if (orden.Equals("fecha"))
                            {
                                cmd.CommandText += " order by r.fecha";
                            }
                            else
                            {
                                if (orden.Equals("fecha1"))
                                {
                                    cmd.CommandText += " order by e.fecha";
                                }
                                else
                                    cmd.CommandText += " order by " + orden;
                            }
                        }
                            
                    }

                }
                SqlDataReader dr = cmd.ExecuteReader();
                dt.Load(dr);
            }
            catch (Exception ex)
            {
                throw new ApplicationException("Error al cargar la grilla:" + ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return dt;

        }
        public static void darBajaReserva(int id)
        {
            SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConexionBaseDatos"].ConnectionString);
            SqlCommand cmd = new SqlCommand();
            
            try
            {
                cn.Open();
                cmd.Connection = cn;
                cmd.CommandText = "delete from Reserva where id_Reserva=@id";
                cmd.Parameters.Add(new SqlParameter("@id", id));
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {                
                throw new ApplicationException("Erroar al eliminar la reserva:" + ex.Message);

            }
            finally
            {
                cn.Close();

            }            
        }   
    }
}
